<% temporaryfolder=addbs(oprop.appstartpath)+[temp\] *temporaryfolder=[temp\] fromdate=orequest.form('datefrom') todate=orequest.form('dateto') if isnull(fromdate) fromdate=NVL(orequest.querystring('datefrom'),'{}') endif if isnull(todate) todate=NVL(orequest.querystring('dateto'),'{}') endif set date ymd pdate1=ctod(fromdate) pdate2=ctod(todate) set date british usesafe('acctran') usesafe('glcodes') usesafe('accounts') openingbalancealias=sys(2015) closingbalancealias=sys(2015) SELECT [acctran] SET ORDER TO acdate retainedearnings=0 FILEstring=[

GLWISE BALANCE SHEET

]+CHR(13) do case case empty(pdate1) and empty(PDATE2) total to (temporaryfolder+closingbalancealias) ON LEFT(accountid,2) for val(left(accountid,2))<9 fields debit, credit copy structure to (temporaryfolder+openingbalancealias) FILEstring=FILESTRING+[

FROM START TO END

] case empty(pdate1) and not empty(PDATE2) total to (temporaryfolder+closingbalancealias) ON LEFT(accountid,2) for val(left(accountid,2))<9 and date<=pdate2 fields debit, credit copy structure to (temporaryfolder+openingbalancealias) FILEstring=FILESTRING+[

FROM START TO ]+DTOC(PDATE2)+[

] case not empty(pdate1) and empty(pdate2) total to (temporaryfolder+closingbalancealias) ON LEFT(accountid,2) for val(left(accountid,2))<9 and date>=pdate1 fields debit, credit total to (temporaryfolder+openingbalancealias) ON LEFT(accountid,2) for val(left(accountid,2))<9 and dateFROM ]+DTOC(PDATE1)+[ TO END] otherwise total to (temporaryfolder+closingbalancealias) ON LEFT(accountid,2) for val(left(accountid,2))<9 and between(date,pdate1,pdate2) fields debit, credit total to (temporaryfolder+openingbalancealias) ON LEFT(accountid,2) for val(left(accountid,2))<9 and dateFROM ]+DTOC(PDATE1)+[ TO ]+DTOC(PDATE2)+[] ENDCASE USE ((temporaryfolder+openingbalancealias+[.dbf])) IN select(openingbalancealias) select (openingbalancealias) CALCULATE SUM(debit-credit) TO retainedearnings USE ((temporaryfolder+closingbalancealias+[.dbf])) IN select(closingbalancealias) select (closingbalancealias) CALCULATE SUM(debit-credit) TO profitfortheperiod select * from (temporaryfolder+closingbalancealias) union all select * from (temporaryfolder+openingbalancealias) order by accountid, date ; into cursor temporaycursor use in select(openingbalancealias) use in select(closingbalancealias) ERASE ((temporaryfolder+closingbalancealias+[.dbf])) ERASE ((temporaryfolder+openingbalancealias+[.dbf])) SELECT [temporaycursor] GO top STORE 0 TO gldebits, glcredits filestring=FILESTRING+CHR(13)+[] DO WHILE NOT EOF() glstring=[] thisac=accountid thisgl=LEFT(accountid,2) =SEEK(thisgl,[glcodes],[glcode]) thisglname=ALLTRIM(glcodes.name) STORE 0 TO glbalance glhead=[] DO WHILE LEFT(accountid,2)=thisgl AND NOT EOF() glbalance=glbalance+debit-credit skip ENDDO IF glbalance>0 glhead=[] gldebits=gldebits+glbalance ENDIF IF glbalance<0 glhead=[] glcredits=glcredits-glbalance ENDIF IF NOT EMPTY(glhead) filestring=filestring+glhead+CHR(13) endif ENDDO IF retainedearnings>0 glhead=[] filestring=filestring+glhead+CHR(13) glcredits=glcredits+retainedearnings ENDIF IF retainedearnings<0 glhead=[] filestring=filestring+glhead+CHR(13) gldebits=gldebits+ABS(retainedearnings) ENDIF IF profitfortheperiod>0 glhead=[] filestring=filestring+glhead+CHR(13) glcredits=glcredits+profitfortheperiod ENDIF IF profitfortheperiod <0 glhead=[] filestring=filestring+glhead+CHR(13) gldebits=gldebits+ABS(profitfortheperiod) ENDIF glhead=[] filestring=filestring+glhead+CHR(13)+[
DETAILSDEBITCREDIT
]+thisglname+[]+TRANSFORM(glbalance,'999,999,999,999.99')+[ 
]+thisglname+[ ]+TRANSFORM(abs(glbalance),'999,999,999,999.99')+[
Retained Earnings ]+TRANSFORM(ABS(retainedearnings),'999,999,999,999.99')+[
Retained Loss]+TRANSFORM(ABS(retainedearnings),'999,999,999,999.99')+[ 
Profit for the period ]+TRANSFORM(ABS(profitfortheperiod),'999,999,999,999.99')+[
Loss for the period]+TRANSFORM(ABS(profitfortheperiod),'999,999,999,999.99')+[ 
OVER ALL TOTALS]+TRANSFORM(ABS(gldebits),'999,999,999,999.99')+[]+TRANSFORM(ABS(glcredits),'999,999,999,999.99')+[
] %>

<%=xcompany.name%>

<%=FILESTRING%>